﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;

namespace ThirdPrize.Classes
{
    public class VoteShowResults
    {
        /// <summary>
        /// </summary>
        /// <returns></returns>
        public string GetRawResults()
        {
            string sql="SELECT [Title], [User] FROM [Totals] ORDER BY [User] DESC;";

            using (var myConn = new SqlConnection())
            {

                myConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

                myConn.Open();

                using (var myCom1 = new SqlCommand(sql, myConn))
                {
                    SqlDataReader reader = myCom1.ExecuteReader();

                    var result = new StringBuilder();

                    result.Append("**");

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            result.Append(reader["Title"]).Append("-").Append(reader["User"]).Append("|");
                        }
                    }
                    else
                    {
                         result.Append("Data Unavailable").Append("-").Append("0");
                    }
                    result.Append("**");

                    result.Append(GetNoOfVotes());

                    result.Append("**");

                    return result.ToString();
                }
            }
        }


        /// <summary>
        /// </summary>
        /// <returns></returns>
        private static int GetNoOfVotes()
        {
            int reader = GetInt("select COUNT (distinct UserId ) from Votes;");

            return reader;
        }

        private static int GetInt(string theSql)
        {
            using (var myConn = new SqlConnection())
            {
                myConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                myConn.Open();

                using (var myCom1 = new SqlCommand(theSql, myConn))
                {
                    int v = Int32.Parse(myCom1.ExecuteScalar().ToString());

                    return v;
                }
            }
        }
    }
}